How to Import Large Databases in Local by Flywheel Sites

Working with clients with WordPress sites can be difficult when you have to migrate their site locally so you can develop on it. If you’re using Local by Flywheel the site is hosted on Flywheel, you will have no problem. If their site is not a big one, you’ll also have no problem using some of the existing solutions such as WP Migrate DB Pro and alike. But what if your site has large databases (read tens of GB), what to do then?

In this tutorial, I’ll show you how to migrate such large databases in Local by Flywheel through SSH.

If you don’t have Local By Flywheel, please go download it and install it so you could follow this guide. Also, create a site using it. I mostly use the custom environment. This tutorial will also assume that you have commands such as ssh, tar, scp and gunzip installed on your environment. If not, you can still follow most of it (ignore the downloading part).

1. Get the Database

This is an obvious step, right? You will need to get the database. When we’re working with such large databases, you will probably get them gzipped. If not, your database will be either in .sql or in .zip.

Some hosting providers will have an option to export the database and you’ll probably get an SQL file. Then you’ll just download this file.

If you have an SSH connection to that site and you can access MySQL on that site, you can get everything by using SSH because it will be much faster.

Exporting SQL from MySQL

mysqldump -uUSERNAME -p DATABASE > backup.sql

Here you need to define your username and the name of your database. This will also ask you the password for the database. If you don’t know the username and the password, you can read them in wp-config.php.

This will place the SQL file in the folder from where you’ve run the command.

Exporting SQL using WP CLI

Maybe your hosting provider installed WP CLI on your site. You can check that out just by running the command wp in the command line. If that’s correct, then you can export the SQL even easier.

wp db export backup.sql

Minimizing SQL

Is your SQL too big? We can make it much smaller. But be sure that you have those tools also installed on your local machine. If not, Local by Flywheel should have those commands available when you connect to your local site through SSH.

Try typing these commands to find out if they’re defined:

• gunzip
• tar

If both commands tell you something about how to use it, then you have them installed. Let’s get back to our ssh connected site. If we are in the folder where the SQL is located, we can run this:

tar -cvzf backup.sql backup.tar.gz

Download the File with SCP

If you have SSH, you’ll probably have the SCP command also there. If not, find a way to install it or download the optimized file from your host. Now, if you’ve created a site, go to it’s own app folder. On my computer, a site would be installed at /Users/yourname/Local Sites/mysite. We can place ourselves in the app folder (the next command will work on a MacOS).

cd /Users/yourname/Local Sites/mysite/app

Now we can download the file with SCP.

scp username@yoursite.com:/location/to/backup.tar.gz .

With the last dot . we have defined our current directory as the download target.

Unpacking the minimized SQL

To unpack it, we need to unzip it (gunzip it) and also to unpack it from .tar. If you don’t have such commands locally installed, then open your site with SSH in Local by Flywheel.

gunzip backup.tar.gz

We should now get the regular backup.tar in our app folder.

tar -xvf backup.tar .

This should get us our backup.sql right there in our app folder.

2. Importing SQL

This is the most important part. The problem with big databases can sometimes be that there are some errors in the SQL. Sometimes and Index is missing or similar. When you try to import the SQL through WP CLI, it will stop its execution when an error has been triggered.

We can import it with the native mysql command by ignoring errors. Let’s say that our SQL is now in the app folder. We are now inside of our site through SSH and we have access to MySQL there.

mysql -u root -proot -f local < /app/backup.sql

This will now import the SQL and with the option -f we are forcing the import to continue even if an error has been triggered.

Be aware that with big databases, you’ll probably have to wait for a long time. Power your computer (if it’s a laptop) and remove any sleep option. A 50GB database could take about 7-8 hours to import completely.

3. Change URL

Open the site in the Local by Fylwheel database and check out the URL you’ve got from import. This can be found in the *_options table under siteurl. Again, with the help of SSH, connect to the Local by Flywheel. We will use WP CLI to replace the URL.

wp search-replace 'http://yoursite.com' 'http://yourlocalsite.local'

If it’s a https site, then change it like that.

Conclusion

That is about it. To summarize what you need to do:

• Get the database (SQL) into your Local by Flywheel site (in app folder)
• Import the database through mysql command with -f (force) option
• Replace the URLs with WP CLI

Local by Flywheel is a really great tool that enables you to start a local development environment in just a few minutes. And you can easily customize the environment on the go.

Become a Sponsor